package com.duke.shaking.storage;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import com.duke.shaking.BaseActivity;
import com.duke.shaking.pojo.ChatFriend;
import com.duke.shaking.pojo.Message;
import com.duke.shaking.pojo.Order;
import com.sohu.pay.order.OrderInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	private static final String DATABASE_NAME = "shaking-%s.db";  
    private static final int DATABASE_VERSION = 1;
    private static final String TAB_FRIEND = "friend";
    private static final String TAB_MESSAGE = "message";
    private static final String TAB_ORDER = "payorder";
    private static final String DATE_FMT = "yyyy-MM-dd HH:mm:ss";
    
    private static final String USERID = "userId";
    private static final String NICKNAME = "nickname";
    private static final String SEX = "sex";
    private static final String AGE = "age";
    private static final String CITY1 = "city1";
    private static final String CITY2 = "city2";
    private static final String BIRTH = "birth";
    private static final String AVATARURL = "avatarUrl";
    private static final String LEVEL = "level";
    private static final String ISFIRST = "isFirst";
    private static final String MSGCOUNT = "msgCount";
    private static final String UNREADCOUNT = "unreadCount";
    private static final String UNREADCOUNTOFNEWFRIEND = "unreadCountOfNewFriend";
    private static final String LASTACTIVETIME = "lastActiveTime";
    private static final String MSGID = "msgId";
    private static final String FRIENDID = "friendId";
    private static final String DIRECTION = "direction";
    private static final String CONTENT = "content";
    private static final String CREATETIME = "CREATETIME";
    private static final String RECVTIME = "recvTime";
    private static final String ISUNREAD = "isUnread";
    private static final String SENDSTATE = "sendState";
    private static final String ORDERID = "orderId";
    private static final String ORDERNO = "orderNo";
    private static final String ORDERTIME = "orderTime";
    private static final String PRODUCTTYPE = "productType";
    private static final String ORDERSTATE = "orderState";
    private static final String PAYSTATUSCODE = "payStatusCode";
    private static final String PAYRESULT = "payResult";

    public boolean isUserDataEmpty = false;
    private SQLiteDatabase db;
	public DatabaseHelper(Context context, String userMobile) {
		super(context, String.format(DATABASE_NAME, userMobile), 
				null, DATABASE_VERSION);
		db = getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TAB_FRIEND + " ("
                + USERID + " VARCHAR PRIMARY KEY, " 
				+ NICKNAME + " VARCHAR(255), "
                + SEX +" INTEGER, "
                + AGE +" INTEGER, "
				+ CITY1 + " VARCHAR(255), "
				+ CITY2 + " VARCHAR(255), "
				+ BIRTH + " DATE, "
				+ LEVEL + " INTEGER,"
				+ AVATARURL + " VARCHAR(1024), "
				+ ISFIRST + " INTEGER, "
				+ UNREADCOUNTOFNEWFRIEND + " INTEGER, "
				+ LASTACTIVETIME + " TIMESTAMP"
				+ ")");

		db.execSQL("CREATE TABLE IF NOT EXISTS " + TAB_MESSAGE + " (" 
                + MSGID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
                + FRIENDID + " VARCHAR(255), "
                + DIRECTION + " INTEGER, "
				+ CONTENT + " TEXT, "
				+ CREATETIME + " TIMESTAMP, "
				+ RECVTIME + " TIMESTAMP, "
				+ ISUNREAD + " INTEGER, "
				+ SENDSTATE + " INTEGER"
				+ ")");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TAB_ORDER + " (" 
                + ORDERID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
                + ORDERNO + " VARCHAR(255), "
				+ ORDERTIME + " TIMESTAMP, "
				+ PRODUCTTYPE + " INTEGER, "
				+ ORDERSTATE + " INTEGER, "
				+ PAYSTATUSCODE + " VARCHAR(255), "
				+ PAYRESULT + " VARCHAR(255)"
				+ ")");
		isUserDataEmpty = true;
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}
	
	private String getString(Cursor res, String colName) {
		return res.getString(res.getColumnIndex(colName));
	}
	
	private int getInt(Cursor res, String colName) {
		return res.getInt(res.getColumnIndex(colName));
	}
	
	private long getLong(Cursor res, String colName) {
		return res.getLong(res.getColumnIndex(colName));
	}
	
	private Date getDate(Cursor res, String colName) {
		String str = getString(res, colName);
		if (str == null) {
			return null;
		}

		try {
			SimpleDateFormat formatter = new SimpleDateFormat(DATE_FMT,
					Locale.CHINA);
			return formatter.parse(str);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}
		return null;
	}
	
	private boolean getBoolean(Cursor res, String colName) {
		int val = getInt(res, colName);
		if (val == 0) {
			return false;
		}
		else {
			return true;
		}
	}
	
	// friends API
	private ChatFriend assembleFriend(Cursor result, String userId) {
		ChatFriend friend = new ChatFriend();
		if (userId == null) {
			friend.setUserId(getString(result, USERID));
		}
		else {
			friend.setUserId(userId);
		}
		friend.setNickname(getString(result, NICKNAME));
		friend.setSex(getInt(result, SEX));
		friend.setAge(getInt(result, AGE));
		friend.setCity1(getString(result, CITY1));
		friend.setCity2(getString(result, CITY2));
		friend.setBirth(getDate(result, BIRTH));
		friend.setLevel(getInt(result, LEVEL));
		friend.setAvatarUrl(getString(result, AVATARURL));
		friend.setNewFriend(getBoolean(result, ISFIRST));
		friend.setTotalMessages(getInt(result, MSGCOUNT));
		if (friend.isNewFriend()) {
			friend.setUnreadMessages(getInt(result, UNREADCOUNTOFNEWFRIEND));
		}
		else {
			friend.setUnreadMessages(getInt(result, UNREADCOUNT));
		}
		friend.setLastMsgTime(getDate(result, LASTACTIVETIME));
		return friend;
	}
	
	public ChatFriend queryFriendByUserId(String userId) {
		/*String []cols = {USERID, NICKNAME, SEX, AGE, CITY1, CITY2, BIRTH, LEVEL,
				AVATARURL, ISFIRST, LASTACTIVETIME};
		String []params = {userId};*/
		String fmt = "SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, "
				+ "(SELECT COUNT(*) FROM %s WHERE %s='%s' and %s=1) AS %s, "
				+ "(SELECT COUNT(*) FROM %s WHERE %s='%s') AS %s FROM %s WHERE %s='%s'";
		String sql = String.format(fmt, USERID, NICKNAME, SEX, AGE, 
				CITY1, CITY2, BIRTH, LEVEL,
				AVATARURL, ISFIRST, LASTACTIVETIME, UNREADCOUNTOFNEWFRIEND,
				TAB_MESSAGE, USERID, userId, ISUNREAD, UNREADCOUNT,
				TAB_MESSAGE, USERID, userId, MSGCOUNT,
				TAB_FRIEND, USERID, userId);
		Cursor result = db.rawQuery(sql, null);
		try {
			if (result == null || result.getCount() <= 0) {
				return null;
			}
			result.moveToFirst();
			if (!result.isAfterLast()) {
				ChatFriend friend = assembleFriend(result, userId);
				return friend;
			}
		}
		finally {
			if (result != null) {
				result.close();
			}
		}
		
		return null;
	}
	
	private ContentValues contentValues(ChatFriend friend) {
		ContentValues cv = new ContentValues();
		cv.put(USERID, friend.getUserId());
		
		if (friend.getNickname() != null) {
			cv.put(NICKNAME, friend.getNickname());
		}
		
		if (friend.getSex() != null) {
			cv.put(SEX, friend.getSex());
		}
	
		if (friend.getAge() != null) {
			cv.put(AGE, friend.getAge());
		}
		
		if (friend.getCity1() != null) {
			cv.put(CITY1, friend.getCity1());
		}
		
		if (friend.getCity2() != null) {
			cv.put(CITY2, friend.getCity2());
		}
		
		if (friend.getBirth() != null) {
			cv.put(BIRTH, date2Str(friend.getBirth()));
		}
		
		if (friend.getAvatarUrl() != null) {
			cv.put(AVATARURL, friend.getAvatarUrl());
		}
		
		if (friend.getLevel() != null) {
			cv.put(LEVEL, friend.getLevel());
		}
		
		/*if (friend.getTotalMessages() != null) {
			cv.put(MSGCOUNT, friend.getTotalMessages());
		}
		
		if (friend.getUnreadMessages() != null) {
			cv.put(UNREADCOUNT, friend.getUnreadMessages());
		}
		*/
		if (friend.isNewFriend() != null) {
			cv.put(ISFIRST, friend.isNewFriend() ? 1 : 0);
			if (friend.isNewFriend()) {
				if (friend.getUnreadMessages() != null) {
					cv.put(UNREADCOUNTOFNEWFRIEND, friend.getUnreadMessages());
				}
			}
			else {
				cv.put(UNREADCOUNTOFNEWFRIEND, 0);
			}
		}
		if (friend.getLastMsgTime() != null) {
			cv.put(LASTACTIVETIME, date2Str(friend.getLastMsgTime()));
		}
		return cv;
	}
	
	public boolean existFriend(ChatFriend friend) {
		String []cols = {USERID};
		String []params = {friend.getUserId()};
		Cursor res = db.query(TAB_FRIEND, cols, USERID + "=?", 
				params, null, null, null);
		if (res != null && res.getCount() > 0) {
			return true;
		}
		return false;
		
	}
	
	public boolean putOrUpdateFriend(ChatFriend friend) {
		
		boolean exist = existFriend(friend);
		
		if (!exist) {
			ContentValues values = contentValues(friend);
			if (db.insert(TAB_FRIEND, null, values) >= 0) {
				return true;
			}
		}
		else {
			ContentValues values = contentValues(friend);
			values.remove(USERID);
			String [] params = {friend.getUserId()};
			if (db.update(TAB_FRIEND, values, USERID+"=?", params) > 0) {
				return true;
			}
		}
		return false;
	}
	
	public List<ChatFriend> loadFriends() {
		/*String []cols = {USERID, NICKNAME, SEX, AGE, CITY1, CITY2, BIRTH, LEVEL,
				AVATARURL, ISFIRST, MSGCOUNT, UNREADCOUNT, LASTACTIVETIME};
		String selection = null;
		String[] selectionArgs = null;
		if (onlyUnread) {
			selection = UNREADCOUNT + ">?";
			selectionArgs = new String[]{"0"};
		}
		
		Cursor result = db.query(TAB_FRIEND, cols, 
				selection, selectionArgs, null, null, 
				ISFIRST + " DESC, " 
				+ UNREADCOUNT + " DESC, "
				+ LASTACTIVETIME + " DESC");*/
		String fmt = "SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, "
				+ "(SELECT COUNT(*) FROM %s WHERE %s=tab1.%s and %s=1) AS %s, "
				+ "(SELECT COUNT(*) FROM %s WHERE %s=tab1.%s) AS %s FROM %s as tab1 "
				+ "ORDER BY %s DESC, %s DESC, %s DESC";
		String sql = String.format(fmt, USERID, NICKNAME, SEX, AGE, 
				CITY1, CITY2, BIRTH, LEVEL,
				AVATARURL, ISFIRST, LASTACTIVETIME, UNREADCOUNTOFNEWFRIEND, 
				TAB_MESSAGE, FRIENDID, USERID, ISUNREAD, UNREADCOUNT,
				TAB_MESSAGE, FRIENDID, USERID, MSGCOUNT,
				TAB_FRIEND, 
				ISFIRST, UNREADCOUNT, LASTACTIVETIME);

		Cursor result = db.rawQuery(sql, null);
		try {
			if (result == null || result.getCount() <= 0) {
				return null;
			}
			
			List<ChatFriend> friends = new ArrayList<ChatFriend>();
			result.moveToFirst();
			while (!result.isAfterLast()) {
				ChatFriend friend = assembleFriend(result, null);
				if (friend != null) {
					friends.add(friend);
				}
				result.moveToNext();
			}
			return friends;
		}
		finally{
			if (result != null) {
				result.close();
			}
		}
	}
	
	public void removeFriendByUserId(String userId) {
		db.delete(TAB_FRIEND, USERID+"=?", new String[]{userId});
	}
	
	public void removeAllFriends() {
		db.delete(TAB_FRIEND, null, null);
	}

	public int updateFriendLastActiveTimeByUserId(String userId, Date lastActiveTime) {
		ContentValues cv = new ContentValues();
		cv.put(LASTACTIVETIME, date2Str(lastActiveTime));
		
		return db.update(TAB_FRIEND, cv, USERID+"=?", new String[]{userId});
	}
	
	// messages API
	private Message assembleMessage(Cursor res) {
		Message msg = new Message();
		msg.setMsgId(getLong(res, MSGID));
		
		int dir = getInt(res, DIRECTION);
		if (dir == Message.MSG_DIRECTION_FROM) {
			msg.setFromId(getString(res, FRIENDID));
			if (BaseActivity.getMe() != null) {
				msg.setToId(BaseActivity.getMe().getUserId());
			}
		}
		else {
			msg.setToId(getString(res, FRIENDID));
			if (BaseActivity.getMe() != null) {
				msg.setFromId(BaseActivity.getMe().getUserId());
			}
		}
		msg.setDirection(dir);
		msg.setContent(getString(res, CONTENT));
		msg.setTimestamp(getDate(res, CREATETIME));
		msg.setRecvTime(getDate(res, RECVTIME));
		msg.setUnread(getBoolean(res, ISUNREAD));
		msg.setSendState(getInt(res, SENDSTATE));
		
		return msg;
	}
	
	public List<Message> loadMessagesByFriendId(String userId, 
			int count, boolean unread, long maxMsgId) {
		String [] cols = {MSGID, FRIENDID, DIRECTION, 
				CONTENT, CREATETIME, RECVTIME, ISUNREAD, SENDSTATE};
		Cursor res;
		String limit = null;
		String order = MSGID;
		String whereMaxMsgId = "";
		if (count > 0) {
			limit = String.valueOf(count);
			order += " DESC";
		}
		if (maxMsgId > 0) {
			whereMaxMsgId = " AND " + MSGID + "<" + maxMsgId;
		}
		if (unread) {
			res = db.query(TAB_MESSAGE, cols, FRIENDID + "=? AND " + ISUNREAD + "=1" + whereMaxMsgId, 
					new String[]{userId}, null, null, order, 
					limit);
		}
		else {
			res = db.query(TAB_MESSAGE, cols, FRIENDID + "=? AND " + ISUNREAD + "=0" + whereMaxMsgId, 
				new String[]{userId}, null, null, order, 
				limit);
		}
		try {
			if (res == null || res.getCount() <= 0) {
				return null;
			}
			
			List<Message> msgs = new ArrayList<Message>();
			
			res.moveToFirst();
			while(!res.isAfterLast()) {
				Message msg = assembleMessage(res);
				if (msg != null) {
					msgs.add(msg);
				}
				res.moveToNext();
			}
			if (msgs.size() > 0) {
				Collections.reverse(msgs);
			}
			return msgs;
		}
		finally {
			if (res != null) {
				res.close();
			}
		}
	}

	private String date2Str(Date date) {
		SimpleDateFormat formatter = new SimpleDateFormat(DATE_FMT,
				Locale.CHINA);
		return formatter.format(date);
	}
	
	private ContentValues contentValues(Message msg) {
		ContentValues cv = new ContentValues();
		if (msg.getContent() != null) {
			cv.put(CONTENT, msg.getContent());
		}
		if (msg.getDirection() != null) {
			cv.put(DIRECTION, msg.getDirection());
			if (msg.getDirection() == Message.MSG_DIRECTION_FROM) {
				cv.put(FRIENDID, msg.getFromId());
			}
			else {
				cv.put(FRIENDID, msg.getToId());
			}
		}
		if (msg.getTimestamp() != null) {
			cv.put(CREATETIME, date2Str(msg.getTimestamp()));
		}
		if (msg.getRecvTime() != null) {
			cv.put(RECVTIME, date2Str(msg.getRecvTime()));
		}
		if (msg.isUnread() != null) {
			cv.put(ISUNREAD, msg.isUnread() ? "1" : "0");
		}
		if (msg.getSendState() != null) {
			cv.put(SENDSTATE, msg.getSendState());
		}
		return cv;
	}
	
	// return the msgId, or -1 if a error occurred
	public long putMessage(Message msg) {
		ContentValues values = contentValues(msg);
		long msgId = db.insert(TAB_MESSAGE, null, values);
		if (msgId >= 0) {
			if (msg.getDirection() != null
				&& msg.getDirection() == Message.MSG_DIRECTION_FROM) {
				updateFriendLastActiveTimeByUserId(msg.getFromId(), msg.getRecvTime());
			}
		}
		return msgId;
	}
	
	public void removeMessageById(long msgId) {
		db.delete(TAB_MESSAGE, MSGID+"=?", new String[]{String.valueOf(msgId)});
	}
	
	public void removeAllMessagesOfFriendByUserId(String userId) {
		db.delete(TAB_MESSAGE, FRIENDID+"=?", new String[]{userId});
	}
	
	public void removeAllMessages() {
		db.delete(TAB_MESSAGE, null, null);
	}
	
	public boolean setMessageReadById(long msgId) {
		ContentValues cv = new ContentValues();
		cv.put(ISUNREAD, 0);
		String [] params = {String.valueOf(msgId)};
		return (db.update(TAB_MESSAGE, cv, MSGID+"=?", params) > 0);
	}
	
	public boolean setMessageSendStateById(long msgId, int sendState) {
		ContentValues cv = new ContentValues();
		cv.put(SENDSTATE, sendState);
		String [] params = {String.valueOf(msgId)};
		return (db.update(TAB_MESSAGE, cv, MSGID+"=?", params) > 0);
	}
	
	public int getUnreadMsgCountFromMsgTab() {
		String fmt = "SELECT COUNT(*) AS %s FROM %s "
				+ "WHERE %s=1";
		String sql = String.format(fmt, UNREADCOUNT, TAB_MESSAGE, ISUNREAD);

		Cursor result = db.rawQuery(sql, null);
		try {
			if (result == null || result.getCount() <= 0) {
				return 0;
			}
			
			result.moveToFirst();
			
			if (!result.isAfterLast()) {
				return getInt(result, UNREADCOUNT);
			}
		}
		finally{
			if (result != null) {
				result.close();
			}
		}
		return 0;
	}
	
	public int getUnreadMsgCountOfNewFriend() {
		String fmt = "SELECT SUM(%s) AS %s FROM %s "
				+ "WHERE %s=1";
		String sql = String.format(fmt, UNREADCOUNTOFNEWFRIEND, 
				UNREADCOUNT, TAB_FRIEND, ISFIRST);

		Cursor result = db.rawQuery(sql, null);
		try {
			if (result == null || result.getCount() <= 0) {
				return 0;
			}
			
			result.moveToFirst();
			
			if (!result.isAfterLast()) {
				
				return getInt(result, UNREADCOUNT);
			}
		}
		finally{
			if (result != null) {
				result.close();
			}
		}
		return 0;
	}
	public int getTotalUnreadMsgCount() {
		return getUnreadMsgCountFromMsgTab() + getUnreadMsgCountOfNewFriend();
	}
	
	public long putOrder(Order order) {
		ContentValues cv = new ContentValues();
		cv.put(ORDERNO, order.getOrderNo());
		cv.put(ORDERTIME, date2Str(order.getOrderTime()));
		cv.put(PRODUCTTYPE, order.getProdectType());
		cv.put(ORDERSTATE, Order.ORDER_STATE_UNFIN);
		cv.put(PAYSTATUSCODE, order.getPayStatusCode());
		cv.put(PAYRESULT, order.getPayResult());
		return db.insert(TAB_ORDER, null, cv);
	}
	
	public void removeOrder(long orderId) {
		db.delete(TAB_ORDER, ORDERID+"=?", new String[]{String.valueOf(orderId)});
	}
	
	public int updateOrderState(Order order) {
		ContentValues cv = new ContentValues();
		
		cv.put(ORDERSTATE, String.valueOf(order.getOrderState()));
		cv.put(PAYSTATUSCODE, String.valueOf(order.getPayStatusCode()));
		cv.put(PAYRESULT, order.getPayResult());
		return db.update(TAB_ORDER, cv, ORDERID+"=?", 
				new String[]{String.valueOf(order.getOrderId())});
	}
	
	public List<Order> loadOrder(boolean onlyUnfinished) {
		String [] cols = {ORDERID, ORDERNO, ORDERTIME, PRODUCTTYPE, ORDERSTATE};
		Cursor res;
		
		if (onlyUnfinished) {
			res = db.query(TAB_ORDER, cols, ORDERSTATE + "=?", 
					new String[]{String.valueOf(Order.ORDER_STATE_UNFIN)}, 
					null, null, ORDERID + " DESC", 
					null);
		}
		else {
			res = db.query(TAB_ORDER, cols, null, 
					null, null, null, ORDERID + " DESC", null);
		}
		
		try {
			if (res == null || res.getCount() <= 0) {
				return null;
			}
			
			List<Order> orders = new ArrayList<Order>();
			
			res.moveToFirst();
			while(!res.isAfterLast()) {
				Order order = new Order();
				order.setOrderId(getInt(res, ORDERID));
				order.setOrderNo(getString(res, ORDERNO));
				order.setOrderTime(getDate(res, ORDERTIME));
				order.setProdectType(getInt(res, PRODUCTTYPE));
				order.setOrderState(getInt(res, ORDERSTATE));
				orders.add(order);
				res.moveToNext();
			}
			
			return orders;
		}
		finally {
			if (res != null) {
				res.close();
			}
		}
	}
}
